package com.cnvp.dbcompare.util;

import com.cnvp.dbcompare.constant.CommonConst;
import com.cnvp.dbcompare.constant.SystemConstant;
import com.cnvp.dbcompare.pojo.dto.DatabaseInfoDto;
import com.cnvp.dbcompare.pojo.cache.CommonData;

import java.io.*;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 工具类
 *
 * @author suqiongbang
 * @date 2021/10/22 21:41
 */
public class Tools {

    /**
     * 数据库连接 缓存map
     */
    private final static Map<Integer, Connection> CONNECTION_CACHE_MAP = new HashMap<>();

    /**
     * mysql保留字
     */
    private final static String MYSQL_RESERVED_WORDS = "ACCESSIBLE,ADD,ALL,ALTER,ANALYZE,AND,AS,ASC,ASENSITIVE,BEFORE,BETWEEN,BIGINT,BINARY,BLOB,BOTH,BY,CALL,CASCADE,CASE,CHANGE,CHAR,CHARACTER,CHECK,COLLATE,COLUMN,CONDITION,CONSTRAINT,CONTINUE,CONVERT,CREATE,CROSS,CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP,CURRENT_USER,CURSOR,DATABASE,DATABASES,DAY_HOUR,DAY_MICROSECOND,DAY_MINUTE,DAY_SECOND,DEC,DECIMAL,DECLARE,DEFAULT,DELAYED,DELETE,DESC,DESCRIBE,DETERMINISTIC,DISTINCT,DISTINCTROW,DIV,DOUBLE,DROP,DUAL,EACH,ELSE,ELSEIF,ENCLOSED,ESCAPED,EXISTS,EXIT,EXPLAIN,FALSE,FETCH,FLOAT,FLOAT4,FLOAT8,FOR,FORCE,FOREIGN,FROM,FULLTEXT,GENERATED[i],GET,GRANT,GROUP,HAVING,HIGH_PRIORITY,HOUR_MICROSECOND,HOUR_MINUTE,HOUR_SECOND,IF,IGNORE,IN,INDEX,INFILE,INNER,INOUT,INSENSITIVE,INSERT,INT,INT1,INT2,INT3,INT4,INT8,INTEGER,INTERVAL,INTO,IO_AFTER_GTIDS,IO_BEFORE_GTIDS,IS,ITERATE,JOIN,KEY,KEYS,KILL,LEADING,LEAVE,LEFT,LIKE,LIMIT,LINEAR,LINES,LOAD,LOCALTIME,LOCALTIMESTAMP,LOCK,LONG,LONGBLOB,LONGTEXT,LOOP,LOW_PRIORITY,MASTER_BIND,MASTER_SSL_VERIFY_SERVER_CERT,MATCH,MAXVALUE,MEDIUMBLOB,MEDIUMINT,MEDIUMTEXT,MIDDLEINT,MINUTE_MICROSECOND,MINUTE_SECOND,MOD,MODIFIES,NATURAL,NOT,NO_WRITE_TO_BINLOG,NULL,NUMERIC,ON,OPTIMIZE,OPTIMIZER_COSTS[r],OPTION,OPTIONALLY,OR,ORDER,OUT,OUTER,OUTFILE,PARTITION,PRECISION,PRIMARY,PROCEDURE,PURGE,RANGE,READ,READS,READ_WRITE,REAL,REFERENCES,REGEXP,RELEASE,RENAME,REPEAT,REPLACE,REQUIRE,RESIGNAL,RESTRICT,RETURN,REVOKE,RIGHT,RLIKE,SCHEMA,SCHEMAS,SECOND_MICROSECOND,SELECT,SENSITIVE,SEPARATOR,SET,SHOW,SIGNAL,SMALLINT,SPATIAL,SPECIFIC,SQL,SQLEXCEPTION,SQLSTATE,SQLWARNING,SQL_BIG_RESULT,SQL_CALC_FOUND_ROWS,SQL_SMALL_RESULT,SSL,STARTING,STORED[ac],STRAIGHT_JOIN,TABLE,TERMINATED,THEN,TINYBLOB,TINYINT,TINYTEXT,TO,TRAILING,TRIGGER,TRUE,UNDO,UNION,UNIQUE,UNLOCK,UNSIGNED,UPDATE,USAGE,USE,USING,UTC_DATE,UTC_TIME,UTC_TIMESTAMP,VALUES,VARBINARY,VARCHAR,VARCHARACTER,VARYING,VIRTUAL[ae],WHEN,WHERE,WHILE,WITH,WRITE,XOR,YEAR_MONTH,ZEROFILL";

    /**
     * 缓存内存数据到文件
     *
     * @param fileName
     */
    public static void setCacheData(String fileName, Object cacheData) {
        String dataDirPath = System.getProperty("user.dir") + File.separator + SystemConstant.FilePath.DATA_DISK_DIR;
        Path dirPath = Paths.get(dataDirPath);
        Path fileDataPath = Paths.get(dirPath + File.separator + fileName);
        try {
            if (!Files.exists(dirPath)) {
                Files.createDirectories(dirPath);
            }
            // 将对象存储在对象输出流中
            if (!Files.exists(fileDataPath)) {
                Files.createFile(fileDataPath);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        // 将内存写入文件
        try (ObjectOutputStream out = new ObjectOutputStream(new FileOutputStream(fileDataPath.toString()))) {
            out.writeObject(cacheData);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 解析文件数据到内存
     *
     * @param fileName
     */
    public static Object getCacheData(String fileName) {
        // SystemConstant.FilePath.databaseInfoDataFileName
        // CommonData.databaseInfoList
        Path fileDataPath = getPath(fileName);
        if (Files.exists(fileDataPath)) {
            try (ObjectInputStream in = new ObjectInputStream(new FileInputStream(fileDataPath.toString()))) {
                return in.readObject();
            } catch (IOException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    private static Path getPath(String fileName) {
        String dataDirPath = System.getProperty("user.dir") + File.separator + SystemConstant.FilePath.DATA_DISK_DIR;
        Path dirPath = Paths.get(dataDirPath);
        return Paths.get(dirPath + File.separator + fileName);
    }

    /**
     * 使用正则表达式来判断字符串中是否包含大写字母
     *
     * @param str 待检验的字符串
     * @return 返回是否包含
     * true: 包含大写字母 ;false 不包含大写字母
     */
    public static boolean isContainUpperCase(String str) {
//        String regex=".*[a-zA-Z]+.*";
        String regex = ".*[A-Z]+.*";
        Matcher m = Pattern.compile(regex).matcher(str);
        return m.matches();
    }

    /**
     * 判断字符串是不是以数字开头
     *
     * @param str
     * @return
     */
    public static boolean isStartWithNumber(String str) {
        String regex = "[0-9].*";
        Matcher m = Pattern.compile(regex).matcher(str.charAt(0) + "");
        return m.matches();
    }

    /**
     * 判断是否是保留字
     *
     * @param word 字
     * @return
     */
    public static boolean isReservedWord(String word) {
        if (MYSQL_RESERVED_WORDS.contains(word)) {
            return true;
        }
        return false;
    }

    /**
     * 根据配置的ID，获取数据库连接实例
     *
     * @param key
     * @return
     */
    public static Connection getConnection(Integer key) {
        Connection connection = CONNECTION_CACHE_MAP.get(key);
        if (null != connection) {
            try {
                // 测试连接有效性
                if (!connection.isValid(3000)) {
                    connection.close();
                    CONNECTION_CACHE_MAP.put(key, buildConnection(key));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return connection;
        } else {
            CONNECTION_CACHE_MAP.put(key, buildConnection(key));
            return CONNECTION_CACHE_MAP.get(key);
        }
    }

    /**
     * 移除数据库连接实例
     *
     * @param key
     */
    public static void removeConnection(Integer key) {
        Connection connection = CONNECTION_CACHE_MAP.get(key);
        if (null != connection) {
            CONNECTION_CACHE_MAP.remove(key);
        }
    }

    /**
     * 构建数据库连接实例
     *
     * @param key
     * @return
     */
    private static Connection buildConnection(Integer key) {
        DatabaseInfoDto databaseInfoDto = CommonData.databaseInfoList.get(key.intValue());
        Connection conn = null;
        try {
            Class.forName(CommonConst.DRIVER_CLASS_NAME);
            String url = CommonConst.URL_PREFIX + databaseInfoDto.getHost() + CommonConst.SYMBOL_COLON
                    + databaseInfoDto.getPort() + CommonConst.SYMBOL_SLANTING_BAR + databaseInfoDto.getDatabase() + CommonConst.URL_SUFFIX;
            conn = DriverManager.getConnection(url, databaseInfoDto.getUserName(), databaseInfoDto.getPassword());
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
}
